package work.database.example.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import work.database.example.connector.CloserUtil;
import work.database.example.dto.Employee;

public class EmployeeDao implements InterfaceDao<Employee>{
	private Connection conn;
	
	public EmployeeDao(Connection conn) {
		this.conn = conn;
	}

	@Override
	public void insertItem(Employee item) throws SQLException {
		String sql = "insert into employee values(?, ?, ?, ?, ?, ?)";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1, item.getEmpNo());
		pstmt.setString(2, item.getEmpName());
		pstmt.setString(3, item.getTitle());
		pstmt.setInt(4, item.getManager());
		pstmt.setInt(5, item.getSalary());
		pstmt.setInt(6, item.getDno());
		pstmt.executeUpdate(); 
		CloserUtil.close(pstmt);		
	}

	@Override
	public void deleteItembyNo(Employee item) throws SQLException {
		String sql = "delete from employee where empno = ?";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1, item.getEmpNo());
		pstmt.executeUpdate();
		CloserUtil.close(pstmt);
	}

	@Override
	public void updateItembyNo(Employee item) throws SQLException {
		String sql = "update employee set empname = ?, title = ?, manager = ?, salary = ?, dno = ? where empno = ?";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, item.getEmpName());
		pstmt.setString(2, item.getTitle());
		pstmt.setInt(3, item.getManager());
		pstmt.setInt(4, item.getSalary());
		pstmt.setInt(5, item.getDno());
		pstmt.setInt(6, item.getEmpNo());
		pstmt.executeUpdate();
		CloserUtil.close(pstmt);		
	}

	@Override
	public Employee searchItem(Employee item) throws SQLException {
		Employee emp = null;
		String sql = "select emeno, empname, title, manager, salary, dno from employee where empno=?";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1, item.getEmpNo());
		ResultSet rs = pstmt.executeQuery();
		
		if(rs.next()){
			emp = new Employee();
			emp.setEmpNo(rs.getInt(1));
			emp.setEmpName(rs.getString(2));
			emp.setTitle(rs.getString(3));
			emp.setManager(rs.getInt(4));
			emp.setSalary(rs.getInt(5));
			emp.setDno(rs.getInt(6));
		}
		return emp;
	}

	@Override
	public ArrayList<Employee> listItems() throws SQLException {
		ArrayList<Employee> list = new ArrayList<>();
		String sql = "select empno, empname, title, manager, salary, dno from employee";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		Employee emp = null;
		while(rs.next()){
			emp = new Employee();
			emp.setEmpNo(rs.getInt(1));
			emp.setEmpName(rs.getString(2));
			emp.setTitle(rs.getString(3));
			emp.setManager(rs.getInt(4));
			emp.setSalary(rs.getInt(5));
			emp.setDno(rs.getInt(6));
			list.add(emp);
			
		}
		return list;
	}

}
